<?php
require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

// 加载Excel文件
$inputFileName = 'xlsx/database.xlsx';
$spreadsheet = IOFactory::load($inputFileName);

// 获取所有工作表
$sheetNames = $spreadsheet->getSheetNames();

// 获取当前选中的工作表索引
$currentSheetIndex = isset($_GET['sheet']) ? $_GET['sheet'] : 0;
// 获取当前工作表
$sheet = $spreadsheet->getSheetByName($sheetNames[$currentSheetIndex]);
$data1 = $sheet->toArray(null, true, true, true);
// 添加数据
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['add'])) {
    $newRow = array($_POST['column1'], $_POST['column2'], $_POST['column3'], $_POST['column4'], $_POST['column5'], $_POST['column6']);
    $sheet->fromArray($newRow, NULL, 'A' . (count($data1) + 1));
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($inputFileName);
}
// 删除数据
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['delete'])) {
    $rowToDelete = $_POST['row'];
    $sheet->removeRow($rowToDelete);
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($inputFileName);
}
// 更新数据
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['update'])) {
    $rowToUpdate = $_POST['row'];
    $colToUpdate = $_POST['col'];
    $newValue = $_POST['value'];
    $sheet->setCellValue($colToUpdate . $rowToUpdate, $newValue);
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($inputFileName);
}
// 显示Tab导航栏
echo "<div id='sheetTabs'>";
foreach ($sheetNames as $index => $sheetName) {
    echo "<a href='?sheet=$index'>$sheetName</a> ";
}
echo "</div>";

$data = $sheet->toArray(null, true, true, true);
// 显示工作表数据
echo "<h2>工作表: {$sheetNames[$currentSheetIndex]}</h2>";
echo "<table border='1'>";
foreach ($data as $rowIndex => $row) {
    echo "<tr>";
    echo "<td>{$rowIndex}</td>";
    foreach ($row as $colIndex => $cell) {
        echo "<td onclick='showEditForm(\"{$rowIndex}\", \"{$colIndex}\", \"{$cell}\")'>{$cell}</td>";
    }
    echo "<td><button onclick='deleteRow({$rowIndex})'>删除</button></td>";
    echo "</tr>";
}
echo "</table>";


?>
<script>
function showEditForm(row, col, value) {
    var form = document.createElement('div');
    form.style.position = 'fixed';
    form.style.top = '50%';
    form.style.left = '50%';
    form.style.transform = 'translate(-50%, -50%)';
    form.style.backgroundColor = '#fff';
    form.style.padding = '20px';
    form.style.border = '1px solid #000';
    form.innerHTML = "<form method='post' >" +
        "<input type='hidden' name='row' value='" + row + "'>" +
        "<input type='hidden' name='col' value='" + col + "'>" +
        "<input type='text' name='value' value='" + value + "'>" +
        "<button type='submit' name='update'>更新</button>" +
        "<button type='button' onclick='closeEditForm()'>关闭</button>" +
        "</form>";
    document.body.appendChild(form);
}

function closeEditForm() {
    var form = document.querySelector('div[style*="position: fixed"]');
    if (form) {
        document.body.removeChild(form);
    }
}

function deleteRow(row) {
    if (confirm('确定要删除该行吗？')) {
        var form = document.createElement('form');
        form.method = 'post';
        form.style.display = 'none';
        var input = document.createElement('input');
        input.type = 'hidden';
        input.name = 'row';
        input.value = row;
        form.appendChild(input);
        var button = document.createElement('input');
        button.type = 'hidden';
        button.name = 'delete';
        form.appendChild(button);
        document.body.appendChild(form);
        form.submit();
    }
}
</script>
<form method="post">
    <h3>添加数据</h3>
    <input type="text" name="column1" placeholder="列1">
    <input type="text" name="column2" placeholder="列2">
    <input type="text" name="column3" placeholder="列3">
    <input type="text" name="column4" placeholder="列4">
    <input type="text" name="column5" placeholder="列5">
    <input type="text" name="column6" placeholder="列6">
    <button type="submit" name="add">添加</button>
</form>